﻿using HCQ2_IDAL;
using HCQ2_Model.DocModel.APIParam;
using HCQ2_Model.DocModel.APIResultModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRErrorWordDAL: IT_VRErrorWordDAL
    {
        /// <summary>
        ///  获取用户错误单词列表
        /// </summary>
        /// <param name="model"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public Task<List<ErrorWordResult>> GetErrorWordByUser(ErrorWordParams model, int user_id)
        {
            sb?.Clear();
            sb.Append($@"select e.file_id,e.cp_order,e.cp_id,e.file_name,e.cp_name,e.err_num,CAST(e.num AS int)AS num,e.word,e.gj_symbol,e.cp_type,e.china_mean,isOk=(case when e.isOk>0 then 1 else 0 end) from(
            select pass.file_name,pass.cp_order,pass.cp_name,pass.cp_code,error.err_num,word.*,isOk=(select count(0) from T_VRExperienceDetail d1 where d1.user_id={user_id} and ed_status=1 {GetDateSql(model)} and d1.word=word.word) from 
            (SELECT *,ROW_NUMBER() OVER(PARTITION BY word ORDER BY word) AS num FROM (SELECT distinct file_id,cp_id,word,gj_symbol,cp_type,china_mean from T_VRCustomPassWord)disWord)word inner join
            (select distinct user_id,if_remove,word,err_num,file_code,cp_code from T_VRErrorWord where user_id={user_id} and if_remove=0 {GetDateSql(model)})error on word.word=error.word
            INNER JOIN (SELECT file_id,cp_id,cp_code,file_name,cp_name,cp_order FROM dbo.T_VRCustomPass)pass ON pass.file_id = word.file_id AND pass.cp_id=word.cp_id AND error.cp_code=pass.cp_code
            INNER JOIN (SELECT file_id,file_code FROM dbo.T_DocumentInfo)info ON info.file_id=pass.file_id AND info.file_code=error.file_code
            )e ORDER by e.word;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return Task.Run(() => { return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ErrorWordResult>(dt); });
        }
        private string GetDateSql(ErrorWordParams model)
        {
            string inDate = string.Empty, outDate = string.Empty;
            StringBuilder query = new StringBuilder();
            if (!string.IsNullOrEmpty(model.startDate))
                inDate = DateTime.ParseExact(model.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.endDate))
                outDate = DateTime.ParseExact(model.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                query.AppendFormat(@" AND create_date>='{0}' AND create_date<='{1}' ", inDate, outDate);
            else if (!string.IsNullOrEmpty(model.startDate) && string.IsNullOrEmpty(model.endDate))
                query.AppendFormat(@" AND create_date>='{0}' ", inDate);
            else if (string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                query.AppendFormat(@" AND create_date<='{0}' ", outDate);
            else
                query.Append(" AND 1=1 ");
            return query.ToString();
        }
    }
}
